﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Npgsql;

namespace VoteInformationSystem.Models
{
    public class NpgsqlKnappsteAusgaengeRepository : IKnappsteAusgaengeRepository
    {
        public IEnumerable<KnappsteAusgaenge> Get(int Jahr)
        {
            IList<KnappsteAusgaenge> knappsteAusgaenge = new List<KnappsteAusgaenge>();
            using (NpgsqlConnection pgConnection = NpgsqlConnectionProvider.GetConnection())
            {
                using (NpgsqlCommand command = new NpgsqlCommand("select vorname, nachname, pname, wname, differenz " +
                                                                 "from \"KnappsteAusgaenge\"(:jahr) " +
                                                                 "join \"Parteien\" using (idpartei) " +
                                                                 "join \"Wahlkreise\" using (idwahlkreis) " +
                                                                 "join \"Personen\" using (idperson) " +
                                                                 "order by \"Personen\".idpartei, differenz",
                                                                 pgConnection))
                {
                    command.Parameters.Add(new NpgsqlParameter("jahr", NpgsqlTypes.NpgsqlDbType.Integer));
                    command.Prepare();
                    command.Parameters.First<NpgsqlParameter>().Value = Jahr;
                    using (NpgsqlDataReader dataReader = command.ExecuteReader())
                    {
                        while (dataReader.Read())
                        {
                            knappsteAusgaenge.Add(new KnappsteAusgaenge()
                                                      {
                                                          Vorname = dataReader.GetString(0),
                                                          Nachname = dataReader.GetString(1),
                                                          Parteiname = dataReader.GetString(2),
                                                          Wahlkreisname = dataReader.GetString(3),
                                                          Differenz = Math.Round(dataReader.GetDecimal(4), 2)
                                                      });
                        }
                    }
                }
            }
            return knappsteAusgaenge;
        }
    }
}